package com.sam;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpSession;
import javax.swing.text.StyledEditorKit.BoldAction;

import com.mysql.jdbc.Driver;

public class MemberDAO {
        public static Connection conn = null;

        public static Connection getConnection() {
                if (conn == null) {
                        try {
                                DriverManager.registerDriver(new Driver());
                                conn = DriverManager.getConnection(
                                                "jdbc:mysql://j.snpy.org/j102u17?useUnicode=true&characterEncoding=UTF-8", "j102u17", "j102u17.989");
                        } catch (SQLException e) {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                        }
                }
                return conn;

        }

        public List<Member> getAll() {
                List<Member> members = new ArrayList<Member>();
                String sql = "select * from users where status=?";
                try {
                	PreparedStatement pstmt = getConnection().prepareStatement(sql);
                	pstmt.setString(1, " ");
                        ResultSet rs = pstmt.executeQuery();
                        while (rs.next()) {
                                String userid = rs.getString("userid");
                                String username = rs.getString("username");
                                String nickname = rs.getString("nickname");
                                String password = rs.getString("password");
                                String email = rs.getString("email");
                                String status = rs.getString("status");
                                String vid = rs.getString("vid");
                                Member member = new Member(userid, username, nickname, password, email, status, vid);
                                members.add(member);
                        }
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return members;
        }

        public int delete(String userid) {
                String sql = "update users set status=? where userid=?";
                int rowCount = 0;
                try {
                        PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        pstmt.setString(1, "Y");
                        pstmt.setString(2, userid);
                        rowCount = pstmt.executeUpdate();
                        System.out.println("delete rowCount = " + rowCount);
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return rowCount;
        }
        
        public int add(Member m){
                String sql = "insert into users(userid,username,nickname,password,email,status,vid) values (?,?,?,?,?,?,?)";
                int rowCount = 0;
                
                if (checkId(m.getUserid())){
                	return rowCount;
                } else { 
                
                	try {
                		PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        pstmt.setString(1, m.getUserid());
                        pstmt.setString(2, m.getUsername());
                        pstmt.setString(3, m.getNickname());
                        pstmt.setString(4, m.getPassword());
                        pstmt.setString(5, m.getEmail());
                        pstmt.setString(6, " ");
                        pstmt.setString(7, " ");
                        rowCount = pstmt.executeUpdate();
                        System.out.println("insertRowCount = " + rowCount);
                		} catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();}
                	return rowCount;
                }
        }
        
        public int update(Member m){
                String sql = "update users set username=?,nickname=?,password=?,email=?  where userid=?";
                int rowCount = 0;
                try {
                        PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        pstmt.setString(1, m.getUsername());
                        pstmt.setString(2, m.getNickname());
                        pstmt.setString(3, m.getPassword());
                        pstmt.setString(4, m.getEmail());
                        pstmt.setString(5, m.getUserid());
                        rowCount = pstmt.executeUpdate();
                        System.out.println("updateRowCount = " + rowCount);
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return rowCount;
        }

        public Member getMemberById(String userid){
                Member member = null;
                String sql = "select * from users where userid=?";
                
                try {
                        PreparedStatement pstmt = getConnection().prepareStatement(sql);
                        pstmt.setString(1, userid);
                        ResultSet rs = pstmt.executeQuery();
                        if (rs.next()){
                                member = new Member(rs.getString("userid")
                                				  , rs.getString("username")
                                                  , rs.getString("nickname")
                                                  , rs.getString("password")
                                                  , rs.getString("email")
                                                  , rs.getString("status")
                                                  , rs.getString("vid"));
                        }
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return member;
        }
        
        public boolean checkId(String userid){
            boolean isuserId = false;
            		
        	String sql = "select * from users where userid=?";
            
            try {
                    PreparedStatement pstmt = getConnection().prepareStatement(sql);
                    pstmt.setString(1, userid);
                    ResultSet rs = pstmt.executeQuery();
                    if (rs.next()){
                    	isuserId = true;
                    }
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            }
            return isuserId;
    }
        
        public boolean login(String userid, String password){
    		String sql = "select * from users where userid=? and password=? and status=?";
    		boolean isLogin = false;
    		
    		try {
    			PreparedStatement pstmt = getConnection().prepareStatement(sql);
                pstmt.setString(1, userid);
                pstmt.setString(2, password);
                pstmt.setString(3, " ");
                ResultSet rs = pstmt.executeQuery();
                if (rs.next()){
                	isLogin = true;
                }
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		
    		return isLogin;
    		
        }    
}